**************************************************************************************
* Bemærk at denne fil kun er til orientering om databaseændringer til denne release. *
* Filen MÅ IKKE afvikles!                                                            *
**************************************************************************************

IF EXISTS (SELECT 1 FROM SYSFOREIGNKEYS WHERE foreign_tname = 'Undervisningssted' and role = 'kommune' ) THEN
  ALTER TABLE Undervisningssted DROP FOREIGN KEY kommune;
END IF;
IF NOT EXISTS (SELECT 1 FROM SYSFOREIGNKEYS WHERE foreign_tname = 'Undervisningssted' and role = 'kommune' ) THEN
  ALTER TABLE Undervisningssted ADD FOREIGN KEY kommune(kommuneKode) REFERENCES PrimaerKommune(primaerKommuneKode) ON UPDATE CASCADE ON DELETE SET NULL;
END IF;

IF EXISTS (SELECT 1 FROM SYSFOREIGNKEYS WHERE foreign_tname = 'Holdgruppe' and role = 'undervisningssprog' ) THEN
  ALTER TABLE Holdgruppe DROP FOREIGN KEY undervisningssprog;
END IF;
IF NOT EXISTS (SELECT 1 FROM SYSFOREIGNKEYS WHERE foreign_tname = 'Holdgruppe' and role = 'undervisningssprog' ) THEN
  ALTER TABLE Holdgruppe ADD FOREIGN KEY undervisningssprog(undervisningssprog) REFERENCES Sprog(sprogKode) ON UPDATE CASCADE;
END IF;

DROP TABLE IF EXISTS KursistPersonnummerHistorik;
CREATE TABLE KursistPersonnummerHistorik
(
    id INTEGER NOT NULL DEFAULT AUTOINCREMENT,
    personnummer varchar(11) not null,
    FOREIGN KEY Kursist(personnummer) REFERENCES Kursist(personnummer) on update cascade on delete cascade,
    oldPersonnummer varchar(11) not null,
    version INTEGER,

    modified DATETIME DEFAULT TIMESTAMP,
PRIMARY KEY (id));


IF NOT EXISTS (select 1 from LokalFag where fagkode = '6GXQV' ) THEN
INSERT INTO LokalFag ("navn", "fagType",  "niveau", "fag", "fagKode", "xprsFagKode", "xprsNiveau", "timetal", "proeveformMundtlig", "proeveformSkriftlig", "skriftligEksaminationstid",   "antalEksaminationerPrDag", "mundtligEksaminationstid","mundtligForberedelsestid","eksamenLokaleForbrug","mundtligAarskarakter","skriftligAarskarakter")
VALUES('Innovation','Gym','A','Innovation A, HHX','6GXQV','5739', 'A' ,325,'X','X',null,null,null,null,null,'X','X');
COMMIT;
END IF;

delete from UMResultatformFag r1
where exists (select 1 from UMResultatformFag r2 where r2.id > r1.id and r1.resformfagId =  r2.resformfagId 
and r1.resultatFormRef = r2.resultatFormRef and r1.umresultatForm_id = r2.umresultatForm_id 
and r1.fagRef = r2.fagRef and r1.xprsfag_Id = r2.xprsfag_Id );
commit;

update UmResultatformFag set version = 0 where version is null; commit;
update umkaraktertype set version = 0 where version = null; commit; 

IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'FtuAnsoegning' AND cname = 'ansoegerFoedselsdato') THEN
  ALTER TABLE FtuAnsoegning ADD ansoegerFoedselsdato DATE NULL;
END IF;

IF EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'FtuAnsoegning' AND cname = 'foedselsdato') THEN
  ALTER TABLE FtuAnsoegning DROP foedselsdato;
END IF;

update  XPRSFagUdd fu
    left join xprsfag
    left join xprsniveau
    left join XPRSUddannelse
    left join xprscoesaformaal cf
set tillaegstakst = 'X'
where
    cf.coesaformaal = '3310' and
    fu.udtraeksversion = 277;
commit;

update elevforloeb ef
set gf1xprsuddannelse_id = null, gf1xprsspeciale_id = null
where not ef.gf1xprsuddannelse_id is null
and type != 'GF1';
commit;

Update EksamenTilmelding t
set t.eksaminationsdato  = null,
    t.eksaminationsstart = null,
    t.eksaminationsslut  = null
where t.eksaminationsdato is not null
  and not exists(
        select 1
        from EksamenDag d
        where d.dato = t.eksaminationsdato
          and d.fagKode = t.fagKode
          and d.holdId = t.holdId
          and d.holdStart = t.holdStart
          and d.proeveform = t.proeveform
          and d.terminKode = t.terminKode);
commit;

DROP TABLE IF EXISTS AlternativTaelledag;
CREATE TABLE AlternativTaelledag(
    taelledag DATE NOT NULL,
    alternativDag DATE NOT NULL,
    bemaerkning VARCHAR(255),
    PRIMARY KEY (taelledag));
COMMIT;

;COMMIT;QUIT;